<?php 
require_once 'BaseMySQLDAO.php';
require_once dirname(__FILE__).DIRECTORY_SEPARATOR.'../Entity/Domain/ScheduleDomain.php';
class ScheduleDAO extends BaseMySQLDAO {

	public function __construct($context) {
		parent::__construct($context, ScheduleDomain::$CollectionName);
	}

	public function create($obj){
		$this->getContext()->getDBTransaction()->startTransaction();
		$inSQL = "INSERT INTO schedule(dwoNumber,deliveryDate,deliveryTime,collectionDate,collectionTime,transportDriver,jobDescription,collectionDateLive,collectionTimeLive,transportDriverLive,jobDescriptionLive,schedule.status,isSent)
					VALUES	(
								".$obj->getDwoNumber().",
								STR_TO_DATE('".$obj->getDeliveryDate()."','%Y-%m-%d'),
								'".$obj->getDeliveryTime()."',
								STR_TO_DATE('".$obj->getCollectionDate()."','%Y-%m-%d'),
								'".$obj->getCollectionTime()."',
								'".$obj->getTransportDriver()."',
								'".$obj->getJobDescription()."',
								STR_TO_DATE('".$obj->getCollectionDateLive()."','%Y-%m-%d'),
								'".$obj->getCollectionTimeLive()."',
								'".$obj->getTransportDriverLive()."',
								'".$obj->getJobDescriptionLive()."',
								".$obj->getStatus().",
								".$obj->getIsSent()."
							)";
		echo $inSQL;
		$this->getMySQLConnection()->mysqlData($inSQL);
		$this->getContext()->getDBTransaction()->closeTransaction();
	}

	public function deleteByOid($oid){
		$this->getContext()->getDBTransaction()->startTransaction();
		$inSQL = "DELETE FROM schedule WHERE scheduleID=".$oid;
		$this->getMySQLConnection()->mysqlData($inSQL);
		$this->getContext()->getDBTransaction()->closeTransaction();
	}

	public function update($obj){
		$this->getContext()->getDBTransaction()->startTransaction();
		$inSQL = "UPDATE schedule
				SET
					schedule.dwoNumber = ".$obj->getDwoNumber().",
					schedule.deliveryDate = STR_TO_DATE('".$obj->getDeliveryDate()."','%Y-%m-%d'),
					schedule.deliveryTime = '".$obj->getDeliveryTime()."',
					schedule.collectionDate = STR_TO_DATE('".$obj->getCollectionDate()."','%Y-%m-%d'),
					schedule.collectionTime = '".$obj->getCollectionTime()."',
					schedule.transportDriver = '".$obj->getTransportDriver()."',
					schedule.jobDescription = '".$obj->getJobDescription()."',
					schedule.collectionDateLive = STR_TO_DATE('".$obj->getCollectionDateLive()."','%Y-%m-%d'),
					schedule.collectionTimeLive = '".$obj->getCollectionTimeLive()."',
					schedule.transportDriverLive = '".$obj->getTransportDriverLive()."',
					schedule.jobDescriptionLive = '".$obj->getJobDescriptionLive()."',
					schedule.status = ".$obj->getStatus().",
					schedule.isSent = ".$obj->getIsSent()."
				 WHERE scheduleID=".$obj->getScheduleID();
		echo $inSQL;
		$this->getMySQLConnection()->mysqlData($inSQL);
		$this->getContext()->getDBTransaction()->closeTransaction();
	}
	
	public function findScheduleByStatus($status)
	{
		$this->getContext()->getDBTransaction()->startTransaction();
		$scheduleDomainArr = array();
		$inSQL = "SELECT * FROM schedule WHERE schedule.status=".$status." ORDER BY scheduleID DESC";
		$res = $this->getMySQLConnection()->mysqlData($inSQL);
		while ($data = mysql_fetch_assoc($res))
		{
			$scheduleDomain = new ScheduleDomain($data);
			$scheduleDomainArr[] = $scheduleDomain;
		}
		$this->getContext()->getDBTransaction()->closeTransaction();
		return $scheduleDomainArr;
	}
	
	public function findScheduleByStatusByDeliveryDate($status,$dateNow,$timeNow, $start, $limit)
	{
		$this->getContext()->getDBTransaction()->startTransaction();
		$scheduleDomainArr = array();
		$inSQL = "SELECT * FROM schedule 
					WHERE schedule.status=".$status." 
							AND ((DATEDIFF(schedule.deliveryDate,'".$dateNow."')<=1 AND schedule.deliveryTime ='00:00:00') 
								OR (schedule.deliveryDate='".$dateNow."' AND TIMEDIFF('".$timeNow."',schedule.deliveryTime)<='02:00:00')
								OR (DATEDIFF(schedule.deliveryDate,'".$dateNow."')<=1)
								) ORDER BY scheduleID DESC LIMIT ".$start.",".$limit;
		$res = $this->getMySQLConnection()->mysqlData($inSQL);
		while ($data = mysql_fetch_assoc($res))
		{
			$scheduleDomain = new ScheduleDomain($data);
			$scheduleDomainArr[] = $scheduleDomain;
		}
		$this->getContext()->getDBTransaction()->closeTransaction();
		return $scheduleDomainArr;
	}
	
	public function findScheduleByStatusByCollectionDate($status,$dateNow,$timeNow, $start, $limit)
	{
		$this->getContext()->getDBTransaction()->startTransaction();
		$scheduleDomainArr = array();
		$inSQL = "SELECT * FROM schedule 
					WHERE schedule.status=".$status." 
							AND ((DATEDIFF(schedule.collectionDate,'".$dateNow."')<=1 AND schedule.collectionTime ='00:00:00') 
								OR (schedule.collectionDate='".$dateNow."' AND TIMEDIFF('".$timeNow."',schedule.collectionTime)<='02:00:00')
								OR (DATEDIFF(schedule.collectionDate,'".$dateNow."')<=1)
								) ORDER BY scheduleID DESC LIMIT ".$start.",".$limit;
		$res = $this->getMySQLConnection()->mysqlData($inSQL);
		while ($data = mysql_fetch_assoc($res))
		{
			$scheduleDomain = new ScheduleDomain($data);
			$scheduleDomainArr[] = $scheduleDomain;
		}
		$this->getContext()->getDBTransaction()->closeTransaction();
		return $scheduleDomainArr;
	}
	
	public function countScheduleByStatusByDeliveryDate($status,$dateNow,$timeNow)
	{
		$this->getContext()->getDBTransaction()->startTransaction();
		$count = 0;
		$inSQL = "SELECT count(scheduleID) FROM schedule 
					WHERE schedule.status=".$status." 
							AND ((DATEDIFF(schedule.deliveryDate,'".$dateNow."')<=1 AND schedule.deliveryTime ='00:00:00') 
								OR (schedule.deliveryDate='".$dateNow."' AND TIMEDIFF('".$timeNow."',schedule.deliveryTime)<='02:00:00')
								OR (DATEDIFF(schedule.deliveryDate,'".$dateNow."')<=1)
								)";
		$res = $this->getMySQLConnection()->mysqlOneField($inSQL);
		$this->getContext()->getDBTransaction()->closeTransaction();
		return $res;
	}
	
	public function countScheduleByStatusByCollectionDate($status,$dateNow,$timeNow)
	{
		$this->getContext()->getDBTransaction()->startTransaction();
		$count = 0;
		$inSQL = "SELECT count(scheduleID) FROM schedule 
					WHERE schedule.status=".$status." 
							AND ((DATEDIFF(schedule.collectionDate,'".$dateNow."')<=1 AND schedule.collectionTime ='00:00:00') 
								OR (schedule.collectionDate='".$dateNow."' AND TIMEDIFF('".$timeNow."',schedule.collectionTime)<='02:00:00')
								OR (DATEDIFF(schedule.collectionDate,'".$dateNow."')<=1)
								)";
		$res = $this->getMySQLConnection()->mysqlOneField($inSQL);
		$this->getContext()->getDBTransaction()->closeTransaction();
		return $res;
	}
	
	public function findAllSchedule ()
	{		
		$this->getContext()->getDBTransaction()->startTransaction();
		$scheduleDomainArr = array();
		$inSQL = "SELECT * FROM schedule ORDER BY scheduleID DESC";
		$res = $this->getMySQLConnection()->mysqlData($inSQL);
		
		while ($data = mysql_fetch_assoc($res))
		{
			$scheduleDomain = new ScheduleDomain($data);
			$scheduleDomainArr[] = $scheduleDomain;
		}
		$this->getContext()->getDBTransaction()->closeTransaction();
		return $scheduleDomainArr;
	}
	
	public function findByOid($oid)
	{		
		$this->getContext()->getDBTransaction()->startTransaction();
		$inSQL = "SELECT * FROM schedule where scheduleID = " . $oid ." ORDER BY scheduleID DESC";
		$res = $this->getMySQLConnection()->mysqlOneRow($inSQL);
		$scheduleDomain = new ScheduleDomain($res);
		$this->getContext()->getDBTransaction()->closeTransaction();
		return $scheduleDomain;
	}
	
	public function updateIsSent($scheduleID, $isSent){
		$this->getContext()->getDBTransaction()->startTransaction();
		$inSQL = "UPDATE schedule
				SET
					schedule.isSent = ".$isSent."
				 WHERE scheduleID=".$scheduleID;
		$this->getMySQLConnection()->mysqlData($inSQL);
		$this->getContext()->getDBTransaction()->closeTransaction();
	}
}
?>